package net.bat.web.dljg;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;

import net.bat.dao.QueryResult;
import net.bat.dao.UserDAO;
import net.bat.dto.EDljgDTO;
import net.bat.dto.EDljgVO;
import net.bat.dto.EDlrVO;
import net.bat.entity.Attach;
import net.bat.entity.EDljg;
import net.bat.entity.EDljgSh;
import net.bat.entity.EDlr;
import net.bat.entity.EKh;
import net.bat.entity.IdEntity;
import net.bat.entity.SDljgAjsl;
import net.bat.entity.SFswx;
import net.bat.entity.User;
import net.bat.service.account.ShiroDbRealm.ShiroUser;
import net.bat.web.api.ApiController;

import org.apache.shiro.SecurityUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.persistence.DynamicSpecifications;
import org.springside.modules.persistence.SearchFilter;

import com.google.common.collect.Lists;

import org.springframework.data.domain.PageImpl;

import net.bat.entity.SDljg;

//Spring Bean的标识.
@Component
// 类中所有public函数都纳入事务管理的标识.
@Transactional
public class DljgService {
	@Autowired
	private UserDAO dao;
	@Autowired
	private DljgDao dljgDao;
	public static final String EN_DLJGSH = "EDljgSh";
	public static final String PN_SHZT = "shzt";

	@Value("${url.fopen}")
	private String url_fopen;
	
	@Value("${url.dljg.attach}")
	private String url_dljg_attach;
	
	@Value("${PRE_JGJJ}")
	private String PRE_JGJJ;
	
	@Value("${PRE_JGRY}")
	private String PRE_JGRY;
	
	@Autowired
	private SDljgDao sdljgDao;
	
	public String getUrlFopen(){
		return url_fopen;
	}
	
	public String getUrlDljgAttach(){
		return url_dljg_attach;
	}
	
	public EDljg getDljg(Long id) {
		return dljgDao.findOne(id);
	}
	//c4w 直接用jpql语句进行多表联合查询,并将查询结果以DTO对象返回
//	public Page<EDljgDTO> getDljgPageForJsly(Map<String, Object> searchParams,Map<String,Object> eparas, 
//			int pageNumber, int pageSize, String sortType,boolean bAsc,String flfs,String flh) {
//		PageRequest pageRequest = buildPageRequest(pageNumber, pageSize, sortType, bAsc);
//		Map<String, SearchFilter> filters = SearchFilter.parse(searchParams);
//		String selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SJsly b";
//		String selectjpql_cout="select count(*) from EDljg a, SJsly b";
//		//TODO 构造where条件和参数值
//		String wherejpql = "a.jgdm=b.jgdm  and b.zb=(?1) and b.jsly = (?2) and b.sl > 0 ";
//		List<Object> queryParams = new ArrayList<Object>();
//		queryParams.add(Integer.parseInt(flfs));
//		if("1".equals(flfs) || "2".equals(flfs)){
//			queryParams.add(flh);
//		}else{
//			queryParams.add(flh.substring(1));
//		}
//		
//		int flag = 3;
//		Set<String> keys = eparas.keySet();
//			if(eparas.containsKey("dy")){
//				String value = (String) eparas.get("dy");
//				String[] dys = value.split("_");
//				String sdys = " and (";
//				for(String str : dys){
//					sdys += " a.dy like (?"+ flag++ +") or ";
//					queryParams.add("%" + str + "%");
//				}
//				
//				wherejpql += sdys.substring(0, sdys.lastIndexOf("or")) + ")";
//			}
//			if(eparas.containsKey("sl_rs")){
//				String value = (String) eparas.get("sl_rs");
//				if(value.contains("lt")){
//					wherejpql += " and a.dlrrs < (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("lt"))));
//				}
//				if(value.contains("-")){
//					wherejpql += " and a.dlrrs >= (?" + flag++ + ") ";
//					wherejpql += " and a.dlrrs < (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.split("-")[0]));
//					queryParams.add(Integer.parseInt(value.split("-")[1]));
//				}
//				if(value.contains("gt")){
//					wherejpql += " and a.dlrrs >= (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("gt"))));
//				}
//				
//			}
//			if(eparas.containsKey("sl_slnx")){
//				String value = (String) eparas.get("sl_slnx");
//				if(value.contains("lt")){
//					Calendar calendar = Calendar.getInstance(); 
//					Date date = calendar.getTime();
//					int year = calendar.get(Calendar.YEAR);
//					calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("lt"))), 0, 0);
//					wherejpql += " and a.dtSlsj < (?" + flag++ + ") ";
//					wherejpql += " and a.dtSlsj > (?" + flag++ + ") ";
//					queryParams.add(date);
//					queryParams.add(calendar.getTime());
//				}
//				if(value.contains("-")){
//					Calendar firstcalendar = Calendar.getInstance(); 
//					Calendar secondcalendar = Calendar.getInstance(); 
//					int year = firstcalendar.get(Calendar.YEAR);
//					firstcalendar.set(year - Integer.parseInt(value.split("-")[1]), 0, 0);
//					secondcalendar.set(year - Integer.parseInt(value.split("-")[0]), 0, 0);
//					
//					wherejpql += " and a.dtSlsj <= (?" + flag++ + ") ";
//					wherejpql += " and a.dtSlsj > (?" + flag++ + ") ";
//					queryParams.add(secondcalendar.getTime());
//					queryParams.add(firstcalendar.getTime());
//				}
//				if(value.contains("gt")){
//					Calendar calendar = Calendar.getInstance(); 
//					Date date = calendar.getTime();
//					int year = calendar.get(Calendar.YEAR);
//					calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("gt"))), 0, 0);
//					wherejpql += " and a.dtSlsj <= (?" + flag++ + ") ";
//					queryParams.add(calendar.getTime());
//					
//				}
//				
//			}
//			if(eparas.containsKey("name")){
//				String value = (String) eparas.get("name");
//				wherejpql += " and a.mc like  (?"+ flag++ +") ";
//				queryParams.add("%"+value.trim()+"%");
//			}
//			
//		System.out.println(wherejpql);
//		//处理排序
//		String orderby=null;
//		if(sortType!=null){
//			if(sortType.equals("auto")){
//				orderby = "a.jgdm";
//			}else{
//				if(bAsc){
//					orderby = sortType;
//				}else{
//					orderby = sortType +" desc";
//				}
//			}
//		}
//		QueryResult<Map> qr = dao.getScrollData(selectjpql, selectjpql_cout, 
//				(pageNumber-1)*pageSize, pageSize, wherejpql, queryParams.toArray(), orderby);
//		Page p = new PageImpl(qr.getResultlist(), pageRequest, qr.getTotalrecord());
//		return p;
//	}
	
	
	public Page<EDljgVO> getDljgPageForJsly(Map<String, Object> searchParams,Map<String,Object> eparas, 
			int pageNumber, int pageSize, String sortType,boolean bAsc,String flfs,String flh) {
		PageRequest pageRequest = buildPageRequest(pageNumber, pageSize, sortType, bAsc);
		StringBuffer sql = new StringBuffer("select a.id, a.jgdm, a.mc, b.sl, a.dt_slsj, a.dlrrs, a.dy from e_dljg a left join s_jsly b");
		String count = "select count(*) from e_dljg a left join s_jsly b on ";
		sql.append(" on ");
		StringBuffer where = new StringBuffer(); 
		StringBuffer condition = new StringBuffer(" where 1=1 ");
		condition.append(" and  b.sl > 0 ");
		String sfl = (String) searchParams.get("EQ_fl");
		where.append(" a.jgdm = b.jgdm ").append(" and b.zb = " + flfs);
		
		if("1".equals(flfs)){
			where.append(" and b.jsly =  " +  "'" + flh + "'");
		}
		if("2".equals(flfs)){
			String temp = null;
			try {
				int s = Integer.parseInt(flh);
				temp = String.valueOf(s);
			} catch (Exception e) {
				temp = flh;
			}
			where.append(" and b.jsly =  " + "'" + temp + "'");
		}
		if("3".equals(flfs)){
			String temp = null;
			try {
				int s = Integer.parseInt(flh.substring(1));
				temp = String.valueOf(s);
			} catch (Exception e) {
				temp = flh.substring(1);
			}
			where.append(" and b.jsly =  " + "'" + temp + "'");
		}
		
		Set<String> keys = eparas.keySet();
		if(eparas.containsKey("dy")){
			String value = (String) eparas.get("dy");
			String[] dys = value.split("_");
			String sdys = " and (";
			for(String str : dys){
				sdys += " a.dy like ("+ "'%" + str + "%'" +") or ";
			}
			condition.append(sdys.substring(0, sdys.lastIndexOf("or")) + ")");
		}
		
		
		if(eparas.containsKey("name")){
			String value = (String) eparas.get("name");
			condition.append("and a.mc like  ('" + "%"+value.trim()+"%"+ "')");
		}
		
		if(eparas.containsKey("sl_rs")){
			String value = (String) eparas.get("sl_rs");
			if(value.contains("lt")){
				condition.append(" and a.dlrrs < " + value.substring(0,value.indexOf("lt")));
			}else if(value.contains("-")){
				condition.append(" and a.dlrrs >= " + value.split("-")[0]);
				condition.append(" and a.dlrrs < " + value.split("-")[1]);
			}else if(value.contains("gt")){
				condition.append(" and a.dlrrs >= " + value.substring(0,value.indexOf("gt")));
			}
			
		}
		
		if(eparas.containsKey("sl_slnx")){
		String value = (String) eparas.get("sl_slnx");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		if(value.contains("lt")){
			Calendar calendar = Calendar.getInstance(); 
			String first = sdf.format(calendar.getTime());
			condition.append(" and a.dt_slsj < " + "'" + first + "'");
			int year = calendar.get(Calendar.YEAR);
			int month = calendar.get(Calendar.MONTH) + 1;
			calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("lt"))), month, 0);
			String second = sdf.format(calendar.getTime());
			condition.append(" and a.dt_slsj > " + "'"+second +"'");
		}else if(value.contains("-")){
			Calendar firstcalendar = Calendar.getInstance(); 
			Calendar secondcalendar = Calendar.getInstance(); 
			int year = firstcalendar.get(Calendar.YEAR);
//			int month = firstcalendar.get(Calendar.MONTH) + 1;
//			int day = firstcalendar.get(Calendar.DAY_OF_MONTH);
			firstcalendar.set(year - Integer.parseInt(value.split("-")[1]), 0, 0);
			secondcalendar.set(year - Integer.parseInt(value.split("-")[0]), 0, 0);
			String first = sdf.format(firstcalendar.getTime());
			String second = sdf.format(secondcalendar.getTime());
			condition.append(" and a.dt_slsj > " + "'" + first + "'");
			condition.append(" and a.dt_slsj <= " + "'"+second +"'");
		}else if(value.contains("gt")){
			Calendar calendar = Calendar.getInstance(); 
			int year = calendar.get(Calendar.YEAR);
			calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("gt"))), 0, 0);
			String first = sdf.format(calendar.getTime());
			condition.append(" and a.dt_slsj <= " + "'" + first + "'");
			
		}
		
	}
			
		System.out.println(sql.toString() + where.toString() + condition.toString() );
		//处理排序
		String orderby=null;
		if(sortType!=null){
			if(sortType.equals("auto")){
				orderby = "a.jgdm";
			}else{
				if(bAsc){
					orderby = sortType;
					if(sortType.equals("a.dt_slsj")){
						orderby = sortType +" desc";
					}
				}else{
					orderby = sortType +" desc";
					if(sortType.equals("a.dt_slsj")){
						orderby = sortType;
					}
				}
			}
		}
		QueryResult<EDljgVO> qr = dao.getScrollDataForOn(sql.toString(),count,where.toString(),(pageNumber-1)*pageSize, pageSize,condition.toString(),orderby);
		Page p = new PageImpl(qr.getResultlist(), pageRequest, qr.getTotalrecord());
		return p;
	}
	
	
	
	
	
//	public Page<EDljgDTO> getDljgPage(Map<String, Object> searchParams,Map<String,Object> eparas, 
//			int pageNumber, int pageSize, String sortType,boolean bAsc) {
//		PageRequest pageRequest = buildPageRequest(pageNumber, pageSize, sortType, bAsc);
//		Map<String, SearchFilter> filters = SearchFilter.parse(searchParams);
//
//		String selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a left join SDljg b";
//		String selectjpql_cout="select count(*) from EDljg a, SDljg b";
//		//TODO 构造where条件和参数值
//		String wherejpql = "a.jgdm=b.jgdm  and b.fl=(?1) and b.nd = (?2) ";
//		List<Object> queryParams = new ArrayList<Object>();
//		//queryParams.add("100002");
//		String sfl = (String) searchParams.get("EQ_fl");
//		//wherejpql += sfl;
//		queryParams.add(sfl);
//		queryParams.add(new Integer(0));
//		int flag = 3;
//		//Map<String,String[]> allParas = request.getParameterMap();
//		Set<String> keys = eparas.keySet();
//		if(eparas.containsKey("sl_fwlx")){
//			String value = (String) eparas.get("sl_fwlx");
//			if(value.equals("zlsq")){
//				//不做处理
//			}
//			if(value.equals("zlfs")){
//				 queryParams.clear();
//				 selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgFswx b";
//				 selectjpql_cout="select count(*) from EDljg a, SDljgFswx b";
//				//TODO 构造where条件和参数值
//				 wherejpql = " b.pid = a.id and b.fl = (?1) ";
//				 queryParams.add(1);
//				 flag = 2;
//			}
//			if(value.equals("zlwx")){
//				queryParams.clear();
//				 selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgFswx b";
//				 selectjpql_cout="select count(*) from EDljg a, SDljgFswx b";
//				//TODO 构造where条件和参数值
//				 wherejpql = " b.pid = a.id and b.fl = (?1) ";
//				 queryParams.add(2);
//				 flag = 2;
//			}
//			if(value.equals("zlxzss")){
//				 queryParams.clear();
//				 selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.xzajsl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgAjsl b";
//				 selectjpql_cout="select count(*) from EDljg a, SDljgAjsl b";
//				//TODO 构造where条件和参数值
//				 wherejpql = "b.jgdm = a.jgdm ";
//				 flag = 1;
//			}
//			if(value.equals("zlmsss")){
//				 queryParams.clear();
//				 selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.msajsl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgAjsl b";
//				 selectjpql_cout="select count(*) from EDljg a, SDljgAjsl b";
//				//TODO 构造where条件和参数值
//				 wherejpql = "b.jgdm = a.jgdm ";
//				 flag = 1;
//			}
//			if(value.equals("zlqsjf")){
//				wherejpql += " and a.zlqsjf > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zljs")){
//				wherejpql += " and a.zljs > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zlfxpy")){
//				wherejpql += " and a.zlfxpj > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zlyj")){
//				wherejpql += " and a.zlyj > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zlyy")){
//				wherejpql += " and a.zlyy > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("qyzscqgb")){
//				wherejpql += " and a.zscqgb > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("xzzfaj")){
//				wherejpql += " and a.xzzfaj > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zldh")){
//				wherejpql += " and a.zldh > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//		}
//			if(eparas.containsKey("dy")){
//				String value = (String) eparas.get("dy");
//				String[] dys = value.split("_");
//				String sdys = " and (";
//				for(String str : dys){
//					sdys += " a.dy like (?"+ flag++ +") or ";
//					queryParams.add("%" + str + "%");
//				}
//				
//				wherejpql += sdys.substring(0, sdys.lastIndexOf("or")) + ")";
//			}
//			if(eparas.containsKey("sl_rs")){
//				String value = (String) eparas.get("sl_rs");
//				if(value.contains("lt")){
//					wherejpql += " and a.dlrrs < (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("lt"))));
//				}
//				if(value.contains("-")){
//					wherejpql += " and a.dlrrs >= (?" + flag++ + ") ";
//					wherejpql += " and a.dlrrs < (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.split("-")[0]));
//					queryParams.add(Integer.parseInt(value.split("-")[1]));
//				}
//				if(value.contains("gt")){
//					wherejpql += " and a.dlrrs >= (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("gt"))));
//				}
//				
//			}
//			if(eparas.containsKey("sl_slnx")){
//				String value = (String) eparas.get("sl_slnx");
//				if(value.contains("lt")){
//					Calendar calendar = Calendar.getInstance(); 
//					Date date = calendar.getTime();
//					int year = calendar.get(Calendar.YEAR);
//					calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("lt"))), 0, 0);
//					wherejpql += " and a.dtSlsj < (?" + flag++ + ") ";
//					wherejpql += " and a.dtSlsj > (?" + flag++ + ") ";
//					queryParams.add(date);
//					queryParams.add(calendar.getTime());
//				}
//				if(value.contains("-")){
//					Calendar firstcalendar = Calendar.getInstance(); 
//					Calendar secondcalendar = Calendar.getInstance(); 
//					int year = firstcalendar.get(Calendar.YEAR);
////					int month = firstcalendar.get(Calendar.MONTH) + 1;
////					int day = firstcalendar.get(Calendar.DAY_OF_MONTH);
//					firstcalendar.set(year - Integer.parseInt(value.split("-")[1]), 0, 0);
//					secondcalendar.set(year - Integer.parseInt(value.split("-")[0]), 0, 0);
//					
//					wherejpql += " and a.dtSlsj <= (?" + flag++ + ") ";
//					wherejpql += " and a.dtSlsj > (?" + flag++ + ") ";
//					queryParams.add(secondcalendar.getTime());
//					queryParams.add(firstcalendar.getTime());
//				}
//				if(value.contains("gt")){
//					Calendar calendar = Calendar.getInstance(); 
//					int year = calendar.get(Calendar.YEAR);
//					calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("gt"))), 0, 0);
//					wherejpql += " and a.dtSlsj <= (?" + flag++ + ") ";
//					queryParams.add(calendar.getTime());
//					
//				}
//				
//			}
//			
//			
//			if(eparas.containsKey("name")){
//				String value = (String) eparas.get("name");
//				wherejpql += " and a.mc like  (?"+ flag++ +") ";
//				queryParams.add("%"+value.trim()+"%");
//			}
//			if(eparas.containsKey("sl_pjqlx")){
//				String value = (String) eparas.get("sl_pjqlx");
//				if(value.contains("lt")){
//					wherejpql += " and b.sl < (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("lt"))));
//				}
//				if(value.contains("-")){
//					wherejpql += " and b.sl >= (?" + flag++ + ") ";
//					wherejpql += " and b.sl < (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.split("-")[0]));
//					queryParams.add(Integer.parseInt(value.split("-")[1]));
//				}
//				if(value.contains("gt")){
//					wherejpql += " and b.sl >= (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("gt"))));
//				}
//			}
//			
//			if(eparas.containsKey("sl_smsys")){
//				String value = (String) eparas.get("sl_smsys");
//				if(value.contains("lt")){
//					wherejpql += " and b.sl < (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("lt"))));
//				}
//				if(value.contains("-")){
//					wherejpql += " and b.sl >= (?" + flag++ + ") ";
//					wherejpql += " and b.sl < (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.split("-")[0]));
//					queryParams.add(Integer.parseInt(value.split("-")[1]));
//				}
//				if(value.contains("gt")){
//					wherejpql += " and b.sl >= (?" + flag++ + ") ";
//					queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("gt"))));
//				}
//			}
//			
//			if(eparas.containsKey("sl_dll")){
//				String value = (String) eparas.get("sl_dll");
//				if(value.contains("lt")){
//					if(eparas.containsKey("sl_fwlx")){
//						String temp = (String) eparas.get("sl_fwlx");
//						if(temp.equals("zlxzss")){
//							 wherejpql += " and b.xzajsl < (?" + flag++ + ") ";
//						}else if(temp.equals("zlmsss")){
//							 wherejpql += " and b.msajsl < (?" + flag++ + ") ";
//						}else {
//							wherejpql += " and b.sl < (?" + flag++ + ") ";
//						}
//					}else{
//						wherejpql += " and b.sl < (?" + flag++ + ") ";
//					}
//					queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("lt"))));
//				}
//				if(value.contains("-")){
//					if(eparas.containsKey("sl_fwlx")){
//						String temp = (String) eparas.get("sl_fwlx");
//						if(temp.equals("zlxzss")){
//							wherejpql += " and b.xzajsl >= (?" + flag++ + ") ";
//							 wherejpql += " and b.xzajsl < (?" + flag++ + ") ";
//						}else if(temp.equals("zlmsss")){
//							wherejpql += " and b.msajsl >= (?" + flag++ + ") ";
//							 wherejpql += " and b.msajsl < (?" + flag++ + ") ";
//						}else{
//							wherejpql += " and b.sl >= (?" + flag++ + ") ";
//							wherejpql += " and b.sl < (?" + flag++ + ") ";
//						}
//					}else{
//						wherejpql += " and b.sl >= (?" + flag++ + ") ";
//						wherejpql += " and b.sl < (?" + flag++ + ") ";
//					}
//					queryParams.add(Integer.parseInt(value.split("-")[0]));
//					queryParams.add(Integer.parseInt(value.split("-")[1]));
//				}
//				if(value.contains("gt")){
//					
//					if(eparas.containsKey("sl_fwlx")){
//						String temp = (String) eparas.get("sl_fwlx");
//						if(temp.equals("zlxzss")){
//							 wherejpql += " and b.xzajsl >= (?" + flag++ + ") ";
//						}else if(temp.equals("zlmsss")){
//							 wherejpql += " and b.msajsl >= (?" + flag++ + ") ";
//						}else{
//							wherejpql += " and b.sl >= (?" + flag++ + ") ";
//						}
//					}else{
//						wherejpql += " and b.sl >= (?" + flag++ + ") ";
//					}
//					queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("gt"))));
//				}
//			}
//			
//			
//		System.out.println( selectjpql + " where "+ wherejpql);
//		//处理排序
//		String orderby=null;
//		if(sortType!=null){
//			if(sortType.equals("auto")){
//				orderby = "a.jgdm";
//			}else{
//				if(bAsc){
//					orderby = sortType;
//				}else{
//					orderby = sortType +" desc";
//				}
//			}
//		}
//		QueryResult<Map> qr = dao.getScrollDataForOn(selectjpql, selectjpql_cout, 
//				(pageNumber-1)*pageSize, pageSize, wherejpql, queryParams.toArray(), orderby);
//		Page p = new PageImpl(qr.getResultlist(), pageRequest, qr.getTotalrecord());
//		return p;
//	}
	
	
	public Page<EDljgVO> getDljgPage(Map<String, Object> searchParams,Map<String,Object> eparas, 
			int pageNumber, int pageSize, String sortType,boolean bAsc) {
		PageRequest pageRequest = buildPageRequest(pageNumber, pageSize, sortType, bAsc);
		StringBuffer sql = new StringBuffer("select a.id, a.jgdm, a.mc, b.sl, a.dt_slsj, a.dlrrs, a.dy from e_dljg a left join s_dljg b");
		String count = "select count(*) from e_dljg a left join s_dljg b on ";
		sql.append(" on ");
		StringBuffer where = new StringBuffer(); 
		StringBuffer condition = new StringBuffer(" where 1=1 ");
		String sfl = (String) searchParams.get("EQ_fl");
		where.append("b.fl = " + sfl).append(" and  b.nd = 0 ").append(" and a.jgdm = b.jgdm ");
		
		if(eparas.containsKey("dy")){
			String value = (String) eparas.get("dy");
			String[] dys = value.split("_");
			String sdys = " and (";
			for(String str : dys){
				sdys += " a.dy like ("+ "'%" + str + "%'" +") or ";
			}
			condition.append(sdys.substring(0, sdys.lastIndexOf("or")) + ")");
		}
		
		if(eparas.containsKey("name")){
			String value = (String) eparas.get("name");
			condition.append("and a.mc like  ('" + "%"+value.trim()+"%"+ "')");
		}
		
		if(eparas.containsKey("sl_rs")){
			String value = (String) eparas.get("sl_rs");
			if(value.contains("lt")){
				condition.append(" and a.dlrrs < " + value.substring(0,value.indexOf("lt")));
			}else if(value.contains("-")){
				condition.append(" and a.dlrrs >= " + value.split("-")[0]);
				condition.append(" and a.dlrrs < " + value.split("-")[1]);
			}else if(value.contains("gt")){
				condition.append(" and a.dlrrs >= " + value.substring(0,value.indexOf("gt")));
			}
			
		}
		
		if(eparas.containsKey("sl_slnx")){
		String value = (String) eparas.get("sl_slnx");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		if(value.contains("lt")){
			Calendar calendar = Calendar.getInstance(); 
			String first = sdf.format(calendar.getTime());
			condition.append(" and a.dt_slsj < " + "'" + first + "'");
			int year = calendar.get(Calendar.YEAR);
			int month = calendar.get(Calendar.MONTH) + 1;
			calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("lt"))), month, 0);
			String second = sdf.format(calendar.getTime());
			condition.append(" and a.dt_slsj > " + "'"+second +"'");
		}else if(value.contains("-")){
			Calendar firstcalendar = Calendar.getInstance(); 
			Calendar secondcalendar = Calendar.getInstance(); 
			int year = firstcalendar.get(Calendar.YEAR);
//			int month = firstcalendar.get(Calendar.MONTH) + 1;
//			int day = firstcalendar.get(Calendar.DAY_OF_MONTH);
			firstcalendar.set(year - Integer.parseInt(value.split("-")[1]), 0, 0);
			secondcalendar.set(year - Integer.parseInt(value.split("-")[0]), 0, 0);
			String first = sdf.format(firstcalendar.getTime());
			String second = sdf.format(secondcalendar.getTime());
			condition.append(" and a.dt_slsj > " + "'" + first + "'");
			condition.append(" and a.dt_slsj <= " + "'"+second +"'");
		}else if(value.contains("gt")){
			Calendar calendar = Calendar.getInstance(); 
			int year = calendar.get(Calendar.YEAR);
			calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("gt"))), 0, 0);
			String first = sdf.format(calendar.getTime());
			condition.append(" and a.dt_slsj <= " + "'" + first + "'");
			
		}
		
	}
		if(eparas.containsKey("sl_fwlx")){
			String value = (String) eparas.get("sl_fwlx");
			if(value.equals("zlsq")){
				condition.append(" and b.sl > 0 ");
			}
			if(value.equals("zlfs")){
				sql  = null ;
				count = null;
				where = null;
				sql = new StringBuffer("select a.id, a.jgdm, a.mc, b.sl, a.dt_slsj, a.dlrrs, a.dy  from e_dljg a left join s_dljg_fswx b on ");
				// selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgFswx b";
				 count = "select count(*) from e_dljg a left join s_dljg_fswx b on ";
				//TODO 构造where条件和参数值
				 where = new StringBuffer(" b.pid = a.id and b.fl = 1 ");
				 condition.append(" and b.sl > 0 ");
			}
			if(value.equals("zlwx")){
				sql  = null ;
				count = null;
				where = null;
				sql = new StringBuffer("select a.id, a.jgdm, a.mc, b.sl, a.dt_slsj, a.dlrrs, a.dy  from e_dljg a left join s_dljg_fswx b on ");
				// selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgFswx b";
				 count = "select count(*) from e_dljg a left join s_dljg_fswx b on ";
				//TODO 构造where条件和参数值
				 where = new StringBuffer(" b.pid = a.id and b.fl = 2 ");
				 condition.append(" and b.sl > 0 ");
			}
			if(value.equals("zlxzss")){
				sql  = null ;
				count = null;
				where = null;
				sql = new StringBuffer("select a.id, a.jgdm, a.mc, b.xzajsl, a.dt_slsj, a.dlrrs, a.dy  from e_dljg a left join s_dljg_ajsl b on ");
				// selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgFswx b";
				 count = "select count(*) from e_dljg a left join s_dljg_ajsl b on ";
				//TODO 构造where条件和参数值
				 where = new StringBuffer(" b.jgdm = a.jgdm ");
				 
				 condition.append(" and b.xzajsl > 0 ");
				 
				 if("b.sl".equals(sortType)){
					 sortType = "b.xzajsl";
				 }
			}
			if(value.equals("zlmsss")){
				
				sql  = null ;
				count = null;
				where = null;
				sql = new StringBuffer("select a.id, a.jgdm, a.mc, b.msajsl, a.dt_slsj, a.dlrrs, a.dy  from e_dljg a left join s_dljg_ajsl b on ");
				// selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgFswx b";
				 count = "select count(*) from e_dljg a left join s_dljg_ajsl b on ";
				//TODO 构造where条件和参数值
				 where = new StringBuffer(" b.jgdm = a.jgdm ");
				 condition.append(" and b.msajsl > 0 ");
				 if("b.sl".equals(sortType)){
					 sortType = "b.msajsl";
				 }
			}
			if(value.equals("zlqsjf")){
				condition.append(" and a.zlqsjf > " + '0');
				condition.append(" and b.sl > 0 ");
			}
			if(value.equals("zljs")){
				condition.append(" and a.zljs > " + '0');
				condition.append(" and b.sl > 0 ");
			}
			if(value.equals("zlfxpy")){
				condition.append(" and a.zlfxpj > " + '0');
				condition.append(" and b.sl > 0 ");
			}
			if(value.equals("zlyj")){
				condition.append(" and a.zlyj > " + '0');
				condition.append(" and b.sl > 0 ");
			}
			if(value.equals("zlyy")){
				condition.append(" and a.zlyy > " + '0');
				condition.append(" and b.sl > 0 ");
			}
			if(value.equals("qyzscqgb")){
				condition.append(" and a.zscqgb > " + '0');
				condition.append(" and b.sl > 0 ");
			}
			if(value.equals("xzzfaj")){
				condition.append(" and a.xzzfaj > " + '0');
				condition.append(" and b.sl > 0 ");
			}
			if(value.equals("zldh")){
				condition.append(" and a.zldh > " + '0');
				condition.append(" and b.sl > 0 ");
			}
		}
		
			if(eparas.containsKey("sl_pjqlx")){
			String value = (String) eparas.get("sl_pjqlx");
			if(value.contains("lt")){
				condition.append(" and b.sl < " + value.substring(0,value.indexOf("lt")));
			}
			if(value.contains("-")){
				condition.append(" and b.sl >= " + value.split("-")[0]);
				condition.append(" and b.sl < " + value.split("-")[1]);
			}
			if(value.contains("gt")){
				condition.append(" and b.sl >= " + value.substring(0,value.indexOf("gt")));
			}
		}
		
		if(eparas.containsKey("sl_smsys")){
			String value = (String) eparas.get("sl_smsys");
			if(value.contains("lt")){
				condition.append(" and b.sl < " + value.substring(0,value.indexOf("lt")));
			}
			if(value.contains("-")){
				condition.append(" and b.sl >= " + value.split("-")[0]);
				condition.append(" and b.sl < " + value.split("-")[1]);
			}
			if(value.contains("gt")){
				condition.append(" and b.sl >= " + value.substring(0,value.indexOf("gt")));
			}
		}
			
			
		if(eparas.containsKey("sl_dll")){
		String value = (String) eparas.get("sl_dll");
		if(value.contains("lt")){
			if(eparas.containsKey("sl_fwlx")){
				String temp = (String) eparas.get("sl_fwlx");
				String pjqlx = (String)searchParams.get("sl_pjqlx");
				String smsys = (String)searchParams.get("sl_smsys");
				if(temp.equals("zlxzss")){
					 condition.append(" and b.xzajsl < " + value.substring(0,value.indexOf("lt")));  
				}else if(temp.equals("zlmsss")){
					 condition.append( " and b.msajsl < " + value.substring(0,value.indexOf("lt")));
				}else if(pjqlx != null || smsys != null){
					sql  = null ;
					count = null;
					where = null;
					condition = null; 
					sql = new StringBuffer("select a.id, a.jgdm, a.mc, b.sl, a.dt_slsj, a.dlrrs, a.dy from e_dljg a left join s_dljg b on ");
					 count = "select count(*) from e_dljg a left join s_dljg b on ";
					 where = new StringBuffer();
					 where.append(" b.nd = 0 ").append(" and a.jgdm = b.jgdm ");
					 condition = new StringBuffer(" where 1 = 1 ");
					 condition.append(" and  (CASE WHEN  ");
					 String fl = (String) searchParams.get("EQ_fl");
					 String dllfl = "100" + fl.substring(3) ;
					 condition.append(" b.fl= " + dllfl).append(" and b.sl < " + value.substring(0,value.indexOf("lt")) + " then 1 when ");
				 	String values = null;
				 	if(pjqlx != null){
				 		values = (String) eparas.get("sl_pjqlx");
				 	}
				 	if(smsys != null){
				 		values = (String) eparas.get("sl_smsys");
				 	}
					if(values.contains("lt")){
						condition.append(" b.fl =  "  + fl +   " and b.sl < " + values.substring(0,values.indexOf("lt")) + " then 1 else 0 end ) = 1");
					}
					if(values.contains("-")){
						condition.append(" b.fl =  "  + fl +   " and b.sl >= " + values.split("-")[0] + " and b.sl < " + values.split("-")[1] +" then 1 else 0 end ) = 1");
					}
					if(values.contains("gt")){
						condition.append(" b.fl =  "  + fl +   " and b.sl >= " + values.substring(0,values.indexOf("gt")) + " then 1 else 0 end ) = 1");
					}
					 
				}else{
					condition.append(" and b.sl < " + value.substring(0,value.indexOf("lt")));
				}
			}else{
			condition.append(" and b.sl < " + value.substring(0,value.indexOf("lt")));
			}
		}
		if(value.contains("-")){
			if(eparas.containsKey("sl_fwlx")){
				String temp = (String) eparas.get("sl_fwlx");
				String pjqlx = (String)searchParams.get("sl_pjqlx");
				String smsys = (String)searchParams.get("sl_smsys");
				if(temp.equals("zlxzss")){
					condition.append( " and b.xzajsl >= " + value.split("-")[0]) ;
					condition.append(" and b.xzajsl < " + value.split("-")[1]);  
				}else if(temp.equals("zlmsss")){
					condition.append( " and b.msajsl >= " + value.split("-")[0]);
					condition.append( " and b.msajsl < " + value.split("-")[1]);
				}else if(pjqlx != null || smsys != null){
					sql  = null ;
					count = null;
					where = null;
					condition = null; 
					sql = new StringBuffer("select a.id, a.jgdm, a.mc, b.sl, a.dt_slsj, a.dlrrs, a.dy from e_dljg a left join s_dljg b on ");
					 count = "select count(*) from e_dljg a left join s_dljg b on ";
					 where = new StringBuffer();
					 where.append(" b.nd = 0 ").append(" and a.jgdm = b.jgdm ");
					 condition = new StringBuffer(" where 1 = 1 ");
					 condition.append(" and  (CASE WHEN  ");
					 String fl = (String) searchParams.get("EQ_fl");
					 String dllfl = "100" + fl.substring(3) ;
					 condition.append(" b.fl= " + dllfl).append(" and b.sl >= " + value.split("-")[0] + " and b.sl <  " + value.split("-")[1] + " then 1 when ");
				 	String values = null;
				 	if(pjqlx != null){
				 		values = (String) eparas.get("sl_pjqlx");
				 	}
				 	if(smsys != null){
				 		values = (String) eparas.get("sl_smsys");
				 	}
					if(values.contains("lt")){
						condition.append(" b.fl =  "  + fl +   " and b.sl < " + values.substring(0,values.indexOf("lt")) + " then 1 else 0 end ) = 1");
					}
					if(values.contains("-")){
						condition.append(" b.fl =  "  + fl +   " and b.sl >= " + values.split("-")[0] + " and b.sl < " + values.split("-")[1] +" then 1 else 0 end ) = 1");
					}
					if(values.contains("gt")){
						condition.append(" b.fl =  "  + fl +   " and b.sl >= " + values.substring(0,values.indexOf("gt")) + " then 1 else 0 end ) = 1");
					}
				}else{
					condition.append(" and b.sl >= " + value.split("-")[0]);
					condition.append(" and b.sl < " + value.split("-")[1]);
				}
			}else{
			condition.append(" and b.sl >= " + value.split("-")[0]);
			condition.append(" and b.sl < " + value.split("-")[1]);
			}
		}
		if(value.contains("gt")){
			if(eparas.containsKey("sl_fwlx")){
				String temp = (String) eparas.get("sl_fwlx");
				String pjqlx = (String)searchParams.get("sl_pjqlx");
				String smsys = (String)searchParams.get("sl_smsys");
				if(temp.equals("zlxzss")){
					condition.append(" and b.xzajsl >= " + value.substring(0,value.indexOf("gt")));  
				}else if(temp.equals("zlmsss")){
					condition.append(" and b.msajsl >= " + value.substring(0,value.indexOf("gt")));  
				}else if(pjqlx != null || smsys != null){
					sql  = null ;
					count = null;
					where = null;
					condition = null; 
					sql = new StringBuffer("select a.id, a.jgdm, a.mc, b.sl, a.dt_slsj, a.dlrrs, a.dy from e_dljg a left join s_dljg b on ");
					 count = "select count(*) from e_dljg a left join s_dljg b on ";
					 where = new StringBuffer();
					 where.append(" b.nd = 0 ").append(" and a.jgdm = b.jgdm ");
					 condition = new StringBuffer(" where 1 = 1 ");
					 condition.append(" and  (CASE WHEN  ");
					 String fl = (String) searchParams.get("EQ_fl");
					 String dllfl = "100" + fl.substring(3) ;
					 condition.append(" b.fl= " + dllfl).append(" and b.sl >= " + value.substring(0,value.indexOf("gt")) + " then 1 when ");
				 	String values = null;
				 	if(pjqlx != null){
				 		values = (String) eparas.get("sl_pjqlx");
				 	}
				 	if(smsys != null){
				 		values = (String) eparas.get("sl_smsys");
				 	}
					if(values.contains("lt")){
						condition.append(" b.fl =  "  + fl +   " and b.sl < " + values.substring(0,values.indexOf("lt")) + " then 1 else 0 end ) = 1");
					}
					if(values.contains("-")){
						condition.append(" b.fl =  "  + fl +   " and b.sl >= " + values.split("-")[0] + " and b.sl < " + values.split("-")[1] +" then 1 else 0 end ) = 1");
					}
					if(values.contains("gt")){
						condition.append(" b.fl =  "  + fl +   " and b.sl >= " + values.substring(0,values.indexOf("gt")) + " then 1 else 0 end ) = 1");
					}
					 
				}else{
					condition.append(" and b.sl >= " + value.substring(0,value.indexOf("gt")));
				}
			}else{
			condition.append(" and b.sl >= " + value.substring(0,value.indexOf("gt")));
			}
		}
	}
			
		System.out.println(sql + "   " + where);
		
		
//		//处理排序
		String orderby=null;
		if(sortType!=null){
			if(sortType.equals("auto")){
				orderby = "a.jgdm";
			}else{
				if(bAsc){
					orderby = sortType;
					if(sortType.equals("a.dt_slsj")){
						orderby = sortType +" desc";
					}
				}else{
					orderby = sortType +" desc";
					if(sortType.equals("a.dt_slsj")){
						orderby = sortType;
					}
				}
			}
		}

		QueryResult<EDljgVO> qr = dao.getScrollDataForOn(sql.toString(),count,where.toString(),(pageNumber-1)*pageSize, pageSize,condition.toString(),orderby);
		Page p = new PageImpl(qr.getResultlist(), pageRequest, qr.getTotalrecord());
		return p;
	}
	
	
	
	
	public Map<String,Object> getDljg(Map<String, Object> searchParams,Map<String,Object> eparas, int pageNumber, int pageSize, String sortType,
			boolean bAsc) {
		PageRequest pageRequest = buildPageRequest(pageNumber, pageSize, sortType, bAsc);
		Map<String, SearchFilter> filters = SearchFilter.parse(searchParams);
		
//		//List<EDljg> f = dljgDao.findAll(spec);
//		Page<SDljg> r1 = sdljgDao.findAll(aa, pageRequest);
		//Page<EDljg> r = dljgDao.findAll(spec, pageRequest);
		Page<EDljg> ed = null;
		List<SDljg> sd = null;
		
		Specification<SDljg> specc = DynamicSpecifications.bySearchFilter(filters.values(), SDljg.class);
		List<SDljg> s =  sdljgDao.findAll(specc);
		Specification<EDljg> conditions = DljgSpecification.Search(sdljgDao.findAll(specc),EDljg.class,eparas);
		ed = dljgDao.findAll(conditions, pageRequest);
		List<EDljg> list = ed.getContent();
		Specification<SDljg> condition = DljgSpecification2.Search(list,SDljg.class,eparas);
		sd = sdljgDao.findAll(condition);
		//}else{
		//	Specification<EDljg> spec = DynamicSpecifications.bySearchFilter(filters.values(), EDljg.class);
			//ed = dljgDao.findAll(spec, pageRequest);
	//	}
		
		Map<String,Object> finalMap = new HashMap<String,Object>();
		finalMap.put("SDljg", sd);
		finalMap.put("EDljg", ed);
		//Page<EDljg> r = dljgDao.findAll(spec, pageRequest);

		return finalMap;
	}

	/**
	 * 创建分页请求.
	 */
	private PageRequest buildPageRequest(int pageNumber, int pagzSize, String sortType, boolean bAsc) {
		Sort sort = null;
		if ("auto".equals(sortType)) {
			sort = new Sort(Direction.DESC, "id");
		} else {
			if (bAsc) {
				sort = new Sort(Direction.ASC, sortType);
			} else {
				sort = new Sort(Direction.DESC, sortType);
			}

		}

		return new PageRequest(pageNumber - 1, pagzSize, sort);
	}
	
	private User getCurrentUser() {
		ShiroUser shiroUser = (ShiroUser) SecurityUtils.getSubject().getPrincipal();
		User user = dao.findByWhere(User.class,"loginName=?",new Object[]{shiroUser.loginName});
		return user;
	}

	private void setSh(String urole,Integer shzt, Map<String, Object> rmap){
		//如果是机构管理员,审核状态除非为‘填报完毕’，否则将其改为‘正在填报’
		User usr = getCurrentUser();
		Integer sh = (Integer)rmap.get(PN_SHZT);
		String ur = usr.getRoles();
		if(ur.equals(ApiController.ROLE_ADMIN_JG)){
			if(sh==null || sh!=2){
				rmap.put(PN_SHZT, 2);
			}			
		}
	}
	
	private void cloneSh(EDljgSh e1) throws InstantiationException, IllegalAccessException{
		EDljg e2 = dao.findByWhere(EDljg.class, "eid=?",new Object[]{e1.getId().intValue()});
		if(e2==null){
			e2 = EDljg.class.newInstance();
			BeanUtils.copyProperties(e1, e2);
			e2.setId(null);
			e2.setEid(e1.getId().intValue());
			dao.persist(e2);
		}else{
			BeanUtils.copyProperties(e1, e2);
			dao.update(e2);			
		}
	}
	/**
	 * 克隆代理机构到填报代理机构表
	 * @param dljg
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @author wangshuxin
	 * 2016-04-20
	 */
	private void cloneEDljgToSh(EDljg dljg) throws InstantiationException, IllegalAccessException{
		EDljgSh dljgsh = dao.find(EDljgSh.class, Long.parseLong(String.valueOf(dljg.getEid())));
		if(dljgsh==null){
			dljgsh = EDljgSh.class.newInstance();
			BeanUtils.copyProperties(dljg, dljgsh);
			dljgsh.setId(null);
			dao.persist(dljgsh);
		}else{
			BeanUtils.copyProperties(dljg, dljgsh);
			dao.update(dljgsh);			
		}
	}
	public EDljgSh update(Long id, Map<String, Object> rmap) throws Exception {
		User usr = getCurrentUser();
		Integer sh = (Integer)rmap.get(PN_SHZT);
		String ur = usr.getRoles();
		setSh(ur, sh, rmap);
		EDljgSh e1 =  (EDljgSh)dao.update(EN_DLJGSH, id, rmap);
		if(ur.equals(ApiController.ROLE_ADMIN_XH) 
				|| ur.equals(ApiController.ROLE_ADMIN)){
			//协议管理员或者超级管理员, 如果审核通过,执行clone动作
			sh = e1.getShzt();
			if(sh!=null && sh==4){
				cloneSh(e1);
			}
		}
		return e1;
	}
	/**
	 * 保存代理机构信息
	 * @param id
	 * @param rmap
	 * @return
	 * @throws Exception
	 * @author wangshuxin
	 * 2016-04-20
	 */
	public EDljg updateEDljg(Long id, Map<String, Object> rmap) throws Exception {
		EDljg dljg =  (EDljg)dao.update("EDljg", id, rmap);
		cloneEDljgToSh(dljg);
		return dljg;
	}
	/*
	 * 获取代理结构详细信息页-基本信息区块中的总代理量值(id 是Edljg中的id)
	 */
	public int getZdll(long id) {
		int sl = 0;
		List<SDljg> objList = dao.queryByWhere(SDljg.class, " fl='100000' and pid=? ", new Object[] { (int) id });
		if(objList != null && objList.size() > 0){
			SDljg obj = objList.get(0);
			sl = obj.getSl();
		}
		return sl;
	}
	/*
	 * 获取代理结构详细信息页-公司简介图片(eid 是Edljg中的eid)
	 */
	public List<Attach> getEDljgjjAttachs(long eid) {
		return dao.queryByWhere(Attach.class, " ename='EDLJGSH' and fname like '"+PRE_JGJJ+"%' and eid=? order by o.ord", new Object[] { (int) eid });
	}
	
	/*
	 * 获取代理结构详细信息页-公司荣誉图片(eid 是Edljg中的eid)
	 */
	public List<Attach> getEDljgryAttachs(long eid) {
		return dao.queryByWhere(Attach.class, " ename='EDLJGSH' and fname like '"+PRE_JGRY+"%' and eid=? order by o.ord", new Object[] { (int) eid });
	}
	
	
	/**
     * 代理结构详细页-代理案量展示区获取数据（当年总量、授权量、授权率、驳回率、国内、国外、港澳台）
     */
	public Map<String,Object> getTotalDljgData(long id,int nd){	
		Map<String,Object> mf = new HashMap<String,Object>();
		int nd1=nd-5,nd2=nd-4,nd3=nd-3,nd4=nd-2,nd5=nd-1; //定义近五年变量
		int fmnd1 = 0,fmnd2 = 0,fmnd3 = 0,fmnd4 = 0,fmnd5 = 0,xxnd1 = 0,xxnd2 = 0,xxnd3 = 0,xxnd4 = 0,xxnd5 = 0,wgnd1 = 0,wgnd2 = 0,wgnd3 = 0,wgnd4 = 0,wgnd5 = 0; //当前总量（发明、新型、外观）	
		int sqfmnd1 = 0,sqfmnd2 = 0,sqfmnd3 = 0,sqfmnd4 = 0,sqfmnd5 = 0,sqxxnd1 = 0,sqxxnd2 = 0,sqxxnd3 = 0,sqxxnd4 = 0,sqxxnd5 = 0,sqwgnd1 = 0,sqwgnd2 = 0,sqwgnd3 = 0,sqwgnd4 = 0,sqwgnd5 = 0; //授权量（发明、新型、外观）
		int sqlnd1 = 0,sqlnd2 = 0,sqlnd3 = 0,sqlnd4 = 0,sqlnd5 = 0,bhlnd1 = 0,bhlnd2 = 0,bhlnd3 = 0,bhlnd4 = 0,bhlnd5 = 0; //授权率、驳回率
		List<SDljg> objlist = dao.queryByWhere(SDljg.class, " pid=?  and nd in ("+nd1+","+nd2+","+nd3+","+nd4+","+nd5+") and "
				+ "(fl like '1001%' OR fl like '1002%' OR fl like '1003%' or fl like '1011%' OR fl like '1012%' OR fl like '1013%' or fl like '102%' or fl like '104%') ", new Object[] { (int) id });
		for(SDljg obj : objlist) {
			String fl = obj.getFl();
			int tmpnd = obj.getNd();
			int sl = obj.getSl();
			if(tmpnd == nd1){
				if(fl.indexOf("1001") != -1){
				     fmnd1 = sl;
				}else if(fl.indexOf("1002") != -1){
					xxnd1 = sl;
				}else if(fl.indexOf("1003") != -1){
					wgnd1 = sl;
				}
				else if(fl.indexOf("1011") != -1){
					sqfmnd1 = sl;
				}
				else if(fl.indexOf("1012") != -1){
					sqxxnd1 = sl;
				}
				else if(fl.indexOf("1013") != -1){
					sqwgnd1 = sl;
				}
				else if(fl.indexOf("102") != -1){
					sqlnd1 = sl;
				}
				else if(fl.indexOf("104") != -1){
					bhlnd1 = sl;
				}						
			}else if(tmpnd == nd2){
				if(fl.indexOf("1001") != -1){
				     fmnd2 = sl;
				}else if(fl.indexOf("1002") != -1){
					xxnd2 = sl;
				}else if(fl.indexOf("1003") != -1){
					wgnd2 = sl;
				}
				else if(fl.indexOf("1011") != -1){
					sqfmnd2 = sl;
				}
				else if(fl.indexOf("1012") != -1){
					sqxxnd2 = sl;
				}
				else if(fl.indexOf("1013") != -1){
					sqwgnd2 = sl;
				}
				else if(fl.indexOf("102") != -1){
					sqlnd2 = sl;
				}
				else if(fl.indexOf("104") != -1){
					bhlnd2 = sl;
				}
						
			}else if(tmpnd == nd3){
				if(fl.indexOf("1001") != -1){
				     fmnd3 = sl;
				}else if(fl.indexOf("1002") != -1){
					xxnd3 = sl;
				}else if(fl.indexOf("1003") != -1){
					wgnd3 = sl;
				}
				else if(fl.indexOf("1011") != -1){
					sqfmnd3 = sl;
				}
				else if(fl.indexOf("1012") != -1){
					sqxxnd3 = sl;
				}
				else if(fl.indexOf("1013") != -1){
					sqwgnd3 = sl;
				}
				else if(fl.indexOf("102") != -1){
					sqlnd3 = sl;
				}
				else if(fl.indexOf("104") != -1){
					bhlnd3 = sl;
				}
						
			}else if(tmpnd == nd4){
				if(fl.indexOf("1001") != -1){
				     fmnd4 = sl;
				}else if(fl.indexOf("1002") != -1){
					xxnd4 = sl;
				}else if(fl.indexOf("1003") != -1){
					wgnd4 = sl;
				}
				else if(fl.indexOf("1011") != -1){
					sqfmnd4 = sl;
				}
				else if(fl.indexOf("1012") != -1){
					sqxxnd4 = sl;
				}
				else if(fl.indexOf("1013") != -1){
					sqwgnd4 = sl;
				}
				else if(fl.indexOf("102") != -1){
					sqlnd4 = sl;
				}
				else if(fl.indexOf("104") != -1){
					bhlnd4 = sl;
				}
						
			}else if(tmpnd == nd5){
				if(fl.indexOf("1001") != -1){
				     fmnd5 = sl;
				}else if(fl.indexOf("1002") != -1){
					xxnd5 = sl;
				}else if(fl.indexOf("1003") != -1){
					wgnd5 = sl;
				}
				else if(fl.indexOf("1011") != -1){
					sqfmnd5 = sl;
				}
				else if(fl.indexOf("1012") != -1){
					sqxxnd5 = sl;
				}
				else if(fl.indexOf("1013") != -1){
					sqwgnd5 = sl;
				}
				else if(fl.indexOf("102") != -1){
					sqlnd5 = sl;
				}
				else if(fl.indexOf("104") != -1){
					bhlnd5 = sl;
				}
					
			}
		}
		//当前总量
		mf.put("fmnd1", fmnd1);
		mf.put("fmnd2", fmnd2);
		mf.put("fmnd3", fmnd3);
		mf.put("fmnd4", fmnd4);
		mf.put("fmnd5", fmnd5);
		mf.put("xxnd1", xxnd1);
		mf.put("xxnd2", xxnd2);
		mf.put("xxnd3", xxnd3);
		mf.put("xxnd4", xxnd4);
		mf.put("xxnd5", xxnd5);
		mf.put("wgnd1", wgnd1);
		mf.put("wgnd2", wgnd2);
		mf.put("wgnd3", wgnd3);
		mf.put("wgnd4", wgnd4);
		mf.put("wgnd5", wgnd5);
		//授权量
		mf.put("sqfmnd1", sqfmnd1);
		mf.put("sqfmnd2", sqfmnd2);
		mf.put("sqfmnd3", sqfmnd3);
		mf.put("sqfmnd4", sqfmnd4);
		mf.put("sqfmnd5", sqfmnd5);
		mf.put("sqxxnd1", sqxxnd1);
		mf.put("sqxxnd2", sqxxnd2);
		mf.put("sqxxnd3", sqxxnd3);
		mf.put("sqxxnd4", sqxxnd4);
		mf.put("sqxxnd5", sqxxnd5);
		mf.put("sqwgnd1", sqwgnd1);
		mf.put("sqwgnd2", sqwgnd2);
		mf.put("sqwgnd3", sqwgnd3);
		mf.put("sqwgnd4", sqwgnd4);
		mf.put("sqwgnd5", sqwgnd5);
		//授权率
		mf.put("sqlnd1", sqlnd1);
		mf.put("sqlnd2", sqlnd2);
		mf.put("sqlnd3", sqlnd3);
		mf.put("sqlnd4", sqlnd4);
		mf.put("sqlnd5", sqlnd5);
		//驳回率
		mf.put("bhlnd1", bhlnd1);
		mf.put("bhlnd2", bhlnd2);
		mf.put("bhlnd3", bhlnd3);
		mf.put("bhlnd4", bhlnd4);
		mf.put("bhlnd5", bhlnd5);
		
		return 	mf;	
	}
	/**
     * 
     * 代理结构详细页-代理案量展示区获取数据（国内、国外、港澳台）
     */

	public Map<String,Object> getTotalAjDljgData(long id,int nd){	
		Map<String,Object> mf = new HashMap<String,Object>();
//		int nd1=nd-5,nd2=nd-4,nd3=nd-3,nd4=nd-2,nd5=nd-1; //定义近五年变量
		int gnyear5 = 0,gwyear5 = 0,gatyear5 = 0;  //(国内、国外、港澳台)
		List<EKh> objlist = dao.queryByWhere(EKh.class, " pid=? ", new Object[] { (int) id });
		for(EKh obj : objlist) {
			int gnw = obj.getGnw();
			int sl = obj.getSl();
			if(gnw == 1){
				gnyear5 +=sl;
			}
			else if(gnw == 2){
				gwyear5 +=sl;
			}
			else if(gnw == 3){
				gatyear5 +=sl;
			}	
		}
		//国内、国外、港澳台
		mf.put("gnyear5", gnyear5);
		mf.put("gwyear5", gwyear5);
		mf.put("gatyear5", gatyear5);
		return 	mf;	
	}
	/**
     * 代理结构详细页-代理案量展示区-专利行政诉讼\专利民事诉讼
     */
	public Map<String,Object> getSDljgAjslInfo(long id){	
		Map<String,Object> mf = new HashMap<String,Object>();
		int msajsl = 0,xzajsl = 0;//专利行政诉讼\专利民事诉讼	
		SDljgAjsl obj = dao.findByWhere(SDljgAjsl.class, " pid=? ", new Object[] { (int) id });
		if(obj != null){
			msajsl = obj.getMsajsl();
			xzajsl = obj.getXzajsl();
		}
		mf.put("msajsl", msajsl);
		mf.put("xzajsl", xzajsl);
		return 	mf;	
	}
	/**
     * 代理结构详细页-代理案量展示区-专利复审\专利无效宣告
     */
	public Map<String,Object> getSDljgFswxInfo(long id){	
		Map<String,Object> mf = new HashMap<String,Object>();
		int fs = 0,wx = 0;  //专利复审\专利无效宣告	
		List<SFswx> objlist = dao.queryByWhere(SFswx.class, " pidDljg=? ", new Object[] { (int) id });
		for(SFswx obj : objlist) {
			int fl = obj.getFl();
			if(fl == 1){
				fs += 1;
			}else if(fl == 2){
				wx += 1;
			}
		}
		mf.put("fs", fs);
		mf.put("wx", wx);
		return 	mf;	
	}
	/**
     * 代理机构详细页-人员结构展示区-代理人
     */
	public Map<String,Object> getEDlrdata(long id,int dlrrs,int jinnian){	
		Map<String,Object> mf = new HashMap<String,Object>();
		int ls = 0, ms = 0, xz = 0, qt = 0;  //代理人
		int year3 = 0, year5 = 0, year10 = 0, year20 = 0, year21 = 0;
		/*Calendar calendar5lt = Calendar.getInstance(); 
		calendar5lt.set(jinnian - 5, 0, 0);
		Calendar calendar5gt = Calendar.getInstance(); 
		Calendar calendar10lt = Calendar.getInstance(); 		
		calendar5gt.set(jinnian - 5, 12, 31);
		calendar10lt.set(jinnian - 10, 0, 0);
		Calendar calendar10gt = Calendar.getInstance(); 
		Calendar calendar15lt = Calendar.getInstance(); 		
		calendar10gt.set(jinnian - 10, 12, 31);
		calendar15lt.set(jinnian - 15, 0, 0);
		Calendar calendar15gt = Calendar.getInstance(); 
		calendar15gt.set(jinnian - 15, 12, 31);*/
		Calendar cl = Calendar.getInstance();
		List<EDlr> objlist = dao.queryByWhere(EDlr.class, " pid=? ", new Object[] { (int) id });
		for(EDlr obj : objlist) {
			int zz = obj.getZz();
			if(zz == 3){
				ls += 1;
			}else if(zz==2){
				ms += 1;
			}else if(zz == 1){
				xz += 1;
			}else{
				qt += 1;
			}
			
			/*Date dt = obj.getDtGzjy();
			if(dt==null || dt.compareTo(calendar5lt.getTime()) >= 0){//2011-2016
				year5 += 1;
			}else if(dt.compareTo(calendar5gt.getTime()) < 0 && dt.compareTo(calendar10lt.getTime()) >= 0){
				year10 += 1;
			}else if(dt.compareTo(calendar10gt.getTime()) < 0 && dt.compareTo(calendar15lt.getTime()) >= 0){
				year15 += 1;
			}else if(dt.compareTo(calendar15gt.getTime()) < 0){
				year16 += 1;
			}*/
			
			Date dt = obj.getDtGzjy();
			if(dt == null)
				year3 += 1;
			else{
				cl.setTime(dt);
				int gznx = jinnian - cl.get(Calendar.YEAR);
				if (gznx < 3)
					year3 += 1;
				else if (gznx >=3 && gznx < 5)
					year5 += 1;
				else if (gznx >=5 && gznx < 10)
					year10 += 1;
				else if (gznx >=10 && gznx < 20)
					year20 += 1;
				else if (gznx >= 20)
					year21 += 1;
			}
		}
		/*int qt = dlrrs-ls-ms-xz;
		if(qt <=0){
			qt = 0;
		}*/
		mf.put("all", ls + ms + xz + qt); //专利代理人
		mf.put("ss", xz + ms);//行政诉讼代理人
		mf.put("qt", qt); //其他代理人
		mf.put("ls", ls); //律师代理人
		mf.put("ms", ms); //民事代理人
		mf.put("xz", xz);
		mf.put("year3", year3);
		mf.put("year5", year5);
		mf.put("year10", year10);
		mf.put("year20", year20);
		mf.put("year21", year21);
		return 	mf;	
	}
         
	public Page<EDlrVO> getDljgRyqPage(Map<String, Object> searchParams,Map<String,Object> eparas, 
			int pageNumber, int pageSize, String sortType,boolean bAsc) {
		PageRequest pageRequest = buildPageRequest(pageNumber, pageSize, sortType, bAsc);
		StringBuffer sql = new StringBuffer("select a.id,a.xm,a.dt_gzjy,a.wynl,b.jgdm,b.mc,b.dy,a.sxzy from e_dlr a left join e_dljg b on a.pid = b.id ");
		String count = "select count(*) from e_dlr a left join e_dljg b  on a.pid = b.id ";

		//TODO 构造where条件和参数值		
		int pid = Integer.parseInt(String.valueOf(searchParams.get("pid")));
		int zz = Integer.parseInt(String.valueOf(searchParams.get("fl")));
		Calendar cl = Calendar.getInstance(); 
		int jinnian = cl.get(Calendar.YEAR);//今年
		int year3 = jinnian-3,year5 = jinnian-5,year10 = jinnian-10,year20 = jinnian-20;	
		String wherejpql = null;
		//代理人分类
		if(zz == 0){
			wherejpql = " where a.pid="+pid+" "; 
		}else if(zz == 12){
			wherejpql = " where a.pid="+pid+" and a.zz in (1,2) "; 
		}else if(zz == 2){
			wherejpql = " where a.pid="+pid+" and a.zz =2 "; 
		}else if(zz == 23){
			wherejpql = " where a.pid="+pid+" and a.zz =3 "; 
		}
		//工作年限
		else if(zz == 3){
			wherejpql = " where a.pid="+pid+" and (a.dt_gzjy is null or YEAR(a.dt_gzjy)>"+year3+") "; 
		}else if(zz == 5){			
			wherejpql = " where a.pid="+pid+" and YEAR(a.dt_gzjy)<="+year3+" and YEAR(a.dt_gzjy)>"+year5+" "; 
		}else if(zz == 10){			
			wherejpql = " where a.pid="+pid+" and YEAR(a.dt_gzjy)<="+year5+" and YEAR(a.dt_gzjy)>"+year10+" "; 
		}else if(zz == 20){		
			wherejpql = " where a.pid="+pid+" and YEAR(a.dt_gzjy)<="+year10+" and YEAR(a.dt_gzjy)>"+year20+" "; 
		}else if(zz == 21){		
			wherejpql = " where a.pid="+pid+" and YEAR(a.dt_gzjy)<="+year20+" "; 
		}

		
		List<Object> queryParams = new ArrayList<Object>();
		queryParams.add(pid);
		String orderby=null;
		if(sortType!=null){
			if(sortType.equals("auto")){
				orderby = "a.dt_gzjy desc";
			}else{
				if(bAsc){
					orderby = sortType;
				}else{
					orderby = sortType +" desc";
				}
			}
		}
		QueryResult<EDlrVO> qr = dao.getScrollDataForRyqDlr(sql.toString(), count, (pageNumber-1)*pageSize, pageSize, wherejpql, orderby);
		Page p = new PageImpl(qr.getResultlist(), pageRequest, qr.getTotalrecord());
		return p;
	}
}
